﻿using System;
using System.Data;
using System.Collections.Generic;
using KPIS.DBM;
using KPIS.GERP.GHRMS.MODEL;
using KPIS.GERP.GHRMS.IDAL;

namespace KPIS.GERP.GHRMS.DAL
{
    public class PerformanceAgreementDAL : IPerformanceAgreementDAL
    {
        private IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public DataTable Search(string strQueryString, string strGoal, string strAction, string strRemark
                                , int? intCreateBy, int? intAgreementSeq)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@perf_agreement_seq", intAgreementSeq);
                dbManager.AddParameters(1, "@emp_seq", strQueryString);
                dbManager.AddParameters(2, "@status", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT perf_agreement_seq, emp_seq, goal_objective, action_plan, remark"
                         + " FROM hrm_perf_agreement "
                         + " WHERE emp_seq = @emp_seq AND record_status not like @status";

                DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                return dtb;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Insert(string strQueryString, string strGoal, string strAction, string strRemark, int? intCreateBy)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(6);

                dbManager.AddParameters(0, "@emp_seq", strQueryString);
                dbManager.AddParameters(1, "@goal_objective", strGoal);
                dbManager.AddParameters(2, "@action_plan", strAction);
                dbManager.AddParameters(3, "@remark", strRemark);
                dbManager.AddParameters(4, "@created_by", intCreateBy);
                dbManager.AddParameters(5, "@record_status", SystemInfo._ActiveRecordStatus);

                strSQL = "INSERT INTO hrm_perf_agreement"
                         + " (emp_seq, "
                         + " goal_objective,"
                         + " action_plan,"
                         + " remark,"
                         + " created_when,"
                         + " created_by,"
                         + " record_status)"
                         + " VALUES"
                         + "( @emp_seq,"
                         + " @goal_objective,"
                         + " @action_plan,"
                         + " @remark,"
                         + " GETDATE(),"
                         + " @created_by,"
                         + " @record_status)";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception error)
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Update(string strQueryString, string strGoal, string strAction, string strRemark, int? intCreateBy,
                           int? intAgreementSeq)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(6);

                dbManager.AddParameters(0, "@emp_seq", strQueryString);
                dbManager.AddParameters(1, "@goal_objective", strGoal);
                dbManager.AddParameters(2, "@action_plan", strAction);
                dbManager.AddParameters(3, "@remark", strRemark);
                dbManager.AddParameters(4, "@created_by", intCreateBy);
                dbManager.AddParameters(5, "@perf_agreement_seq", intAgreementSeq);

                strSQL = "UPDATE hrm_perf_agreement SET"
                         + " emp_seq = @emp_seq, "
                         + " goal_objective = @goal_objective,"
                         + " action_plan = @action_plan,"
                         + " remark = @remark,"
                         + " updated_when = GETDATE(),"
                         + " created_by = @created_by"
                         + " WHERE perf_agreement_seq = @perf_agreement_seq AND emp_seq = @emp_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception error)
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Delete(int? intCreateBy, int? intAgreementSeq, string strQueryString)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@perf_agreement_seq", intAgreementSeq);
                dbManager.AddParameters(1, "@emp_seq", strQueryString);
                dbManager.AddParameters(2, "@created_by", intCreateBy);
                dbManager.AddParameters(3, "@status", SystemInfo._DeleteRecordStatus);

                strSQL = " UPDATE hrm_perf_agreement SET "
                         + " updated_when = GETDATE(),"
                         + " created_by = @created_by,"
                         + " record_status = @status"
                         + " WHERE perf_agreement_seq = @perf_agreement_seq AND emp_seq = @emp_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception error)
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public bool CheckValueSubmit(int? intAgreementSeq)
        {
            string strSQL;

            bool result = true;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@perf_agreement_seq", intAgreementSeq);
                dbManager.AddParameters(1, "@status", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT perf_agreement_seq, emp_seq, goal_objective, action_plan, remark"
                         + " FROM  hrm_perf_agreement"
                         + " WHERE record_status = @status";

                IDataReader rdr = dbManager.ExecuteReader(CommandType.Text, strSQL);

                while (rdr.Read())
                {
                    result = true;
                    break;
                }
            }
            catch (Exception error)
            {
                throw error;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
            return result;
        }

        public bool CheckValueDelete(int? intAgreementSeq)
        {
            string strSQL;

            bool result = true;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@perf_agreement_seq", intAgreementSeq);
                dbManager.AddParameters(1, "@status", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT *"
                         + " FROM  hrm_perf_agreement"
                         + " WHERE record_status = @status AND perf_agreement_seq = @perf_agreement_seq";

                IDataReader rdr = dbManager.ExecuteReader(CommandType.Text, strSQL);

                while (rdr.Read())
                {
                    result = true;
                    break;
                }
            }
            catch (Exception error)
            {
                throw error;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
            return result;
        }
    }
}